package com.pkuse.bjousystem.repository.student;

import com.pkuse.bjousystem.dto.student.XueShengChengJiDTO;
import com.pkuse.bjousystem.dto.student.XueShengJiHuaXiangQingDTO;
import com.pkuse.bjousystem.entity.student.ZongChengJi;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;


public interface ZongChengJiRepository extends CrudRepository<ZongChengJi, String> {

    //本学期成绩
    @Query("select new com.pkuse.bjousystem.dto.student.XueShengChengJiDTO(b.kcmc,a.kscs,a.qdcjsj,a.xkcj,a.sjcj,a.zhcj) " +
            "from ZongChengJi a,KeCheng b WHERE a.kcid=b.kcid" +
            "	 and a.xh = :xh" +
            "	 and a.qdcjsj >= :qdcjsj" +
            "	 order by a.qdcjsj desc")
    Page<XueShengChengJiDTO> listXueShengChengJi(@Param("xh") String xh, @Param("qdcjsj") String qdcjsj, Pageable pageable);

    //往学期成绩
    @Query("select new com.pkuse.bjousystem.dto.student.XueShengChengJiDTO(b.kcmc,a.kscs,a.qdcjsj,a.xkcj,a.sjcj,a.zhcj) " +
            "from ZongChengJi a,KeCheng b WHERE a.kcid=b.kcid" +
            "	 and a.xh = :xh" +
            "	 and a.qdcjsj < :qdcjsj" +
            "	 order by a.qdcjsj desc")
    Page<XueShengChengJiDTO> listWangNianXueShengChengJi(@Param("xh") String xh, @Param("qdcjsj") String qdcjsj, Pageable pageable);

    @Query("select new com.pkuse.bjousystem.dto.student.XueShengJiHuaXiangQingDTO" +
            "(zymkdmb.mkh,zymkdmb.mkm,zymkb.mkxf,zymkb.mkzyxf,mkkcb.kcid,mkkcb.kcmc,kcxzdmb.kcxzmc,kclxdmb.kclxmc,mkkcb.xf,mkkcb.ksxq,ksdwlxdmb.ksdwlxmc,zcjb.zhcj) " +
            "from ZongChengJi zcjb  " +
            "inner join Student xsb on xsb.xh = zcjb.xh and zcjb.xh = :xh " +
            "left join MoKuaiKeCheng mkkcb on zcjb.kcid = mkkcb.kcid and xsb.gzh = mkkcb.gzh " +
            "left join KeChengLeiXingDaiMa kclxdmb on mkkcb.kclxdm=kclxdmb.kclxdm " +
            "left join KaiSheDanWeiLeiXingDaiMa ksdwlxdmb on ksdwlxdmb.ksdwlxdm=mkkcb.ksdwlxdm " +
            "left join ZhuanYeMoKuai zymkb on zymkb.mkh=mkkcb.mkh and zymkb.gzh = xsb.gzh " +
            "left join ZhuanYeMoKuaiDaiMa zymkdmb on zymkdmb.mkh=mkkcb.mkh " +
            "left join KeChengXingZhiDaiMa kcxzdmb on kcxzdmb.kcxzdm=mkkcb.kcxzdm " +
            "where mkkcb.mkh in ( " +
            " select zymkb.mkh " +
            " from Student xsb  " +
            " inner join ZhuanYeMoKuai zymkb on xsb.xh = :xh and xsb.gzh = zymkb.gzh " +
            ") ")
    List<XueShengJiHuaXiangQingDTO> listJiHuaXiangQing(@Param("xh") String xh);

}
